#!/usr/bin/env python
# _*_ coding: utf-8 _*_
# @Time : 2022/10/26 18:42
# @Author : Violet
# @File : film.py
# @desc :电影数据分析接口
import re

import xlrd2
from fastapi import APIRouter
from utils import resp_200
from openpyxl import load_workbook

# ---------------------------------------------美观线-------------------------------------------------------

router = APIRouter()


# dependencies=[Security(check_permissions, scopes=["user_select"])]
@router.get('/release_year', summary="年份上映情况")
async def excel():
    # filename是文件的路径名称
    workbook = xlrd2.open_workbook('./models/film_data.xls')
    # 通过sheet名称获取
    table = workbook.sheet_by_name(sheet_name='Sheet1')
    table_list = table.col_values(colx=2, start_rowx=1, end_rowx=None)

    # year_list={''+str(i)+':'+str(1) for i in range(2008,2023)}
    year_list = {'2008': 1, '2009': 1, '2010': 1, '2011': 1, '2012': 1, '2013': 1, '2014': 1, '2015': 1, '2016': 1,
                 '2017': 1, '2018': 1, '2019': 1, '2020': 1, '2021': 1, '2022': 1}

    for i in range(0, len(table_list)):
        try:
            if year_list.get('' + str(int(table_list[i]))):
                year_list['' + str(int(table_list[i]))] = year_list['' + str(int(table_list[i]))] + 1
        except ValueError:
            pass

    data_list = [year_list[i] for i in year_list]
    return resp_200(msg="查询成功！", data={"data": data_list,
                                       "year": [2008, 2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019,
                                                2020, 2021, 2022]})


@router.get('/language_use', summary="语言使用统计(top10)")
async def excel():
    # filename是文件的路径名称
    workbook = xlrd2.open_workbook('./models/film_data.xls')
    # 通过sheet名称获取
    table = workbook.sheet_by_name(sheet_name='Sheet1')
    table_list = table.col_values(colx=5, start_rowx=1, end_rowx=None)
    result = {}
    # 数据统计
    for i in table_list:
        data = re.split("[ /无,;]", i)
        for j in data:
            if j == '':
                break
            if j not in result:
                result[j] = 1
            else:
                result[j] = result[j] + 1

    result["普通话"] = result["普通话"] + result["国语"]
    result["普通话"] = result["普通话"] + result["汉语普通话"]
    del result["国语"]
    del result["汉语普通话"]
    # 数据排序
    result = sorted(result.items(), key=lambda x: x[1], reverse=True)
    return resp_200(msg="查询成功！", data=result[0:10])


@router.get('/film_type', summary="电影类型统计(top20)")
async def excel():
    # filename是文件的路径名称
    workbook = xlrd2.open_workbook('./models/film_data.xls')
    # 通过sheet名称获取
    table = workbook.sheet_by_name(sheet_name='Sheet1')
    table_list = table.col_values(colx=4, start_rowx=1, end_rowx=None)
    result = {}
    # 数据统计
    for i in table_list:
        data = re.split("[ /无,;]", i)
        for j in data:
            if j == '':
                break
            if j not in result:
                result[j] = 1
            else:
                result[j] = result[j] + 1
    # 数据排序
    result = sorted(result.items(), key=lambda x: x[1], reverse=True)
    return resp_200(msg="查询成功！", data=result[0:20])


@router.get('/country_film_score', summary="各国电影评分(top10)电影数不少于30部")
async def excel():
    # filename是文件的路径名称
    workbook = xlrd2.open_workbook('./models/film_data.xls')
    # 通过sheet名称获取
    table = workbook.sheet_by_name(sheet_name='Sheet1')
    country_list = table.col_values(colx=3, start_rowx=1, end_rowx=None)
    score_list = table.col_values(colx=8, start_rowx=1, end_rowx=None)
    del_list = ["中国大陆", "中国香港", "中国台湾", '']
    result = {}
    score_result = []
    result_list = {}

    for i in score_list:
        # 非数字无法转成float 直接变成0
        try:
            data = re.split("[ /,无]", i)
            score_result.append(float(data[0]))
        except ValueError:
            score_result.append(0)

    for i in range(len(country_list)):
        i_data = re.split("[ /,无N]", country_list[i])
        for data in i_data:
            if data in del_list:
                break
            if data not in result:
                result[data] = {"count": 0, "score": 0}
            else:
                result[data]["count"] = result[data]["count"] + 1
                result[data]["score"] = result[data]["score"] + score_result[i]

    # 求平均数
    for i in result:
        if result[i]["score"] < 0 or result[i]["count"] < 30:
            pass
        else:
            result_list[i] = round(result[i]["score"] / result[i]["count"], 1)
    # 数据排序
    result_list = sorted(result_list.items(), key=lambda x: x[1], reverse=True)

    return resp_200(msg="查询成功！", data=result_list[0:12])


@router.get('/film_time_play', summary="电影时长占比")
async def excel():
    # filename是文件的路径名称
    workbook = xlrd2.open_workbook('./models/film_data.xls')
    # 通过sheet名称获取
    table = workbook.sheet_by_name(sheet_name='Sheet1')
    time_list = table.col_values(colx=9, start_rowx=2, end_rowx=None)
    result = {"30": 0, "60": 0, "90": 0, "120": 0, "150": 0, "180": 0, "210": 0, "更多": 0}
    result_list = []
    tihuan = ''
    for i in time_list:
        data = re.split("[分钟h:m:s Min Mins无]", str(i))
        try:
            if int(data[0]):
                for j in result:
                    if int(data[0]) > 210:
                        result["更多"] = result["更多"] + 1
                        break
                    if int(data[0]) < 30:
                        result["30"] = result["30"] + 1
                        break
                    if int(data[0]) > int(j):
                        tihuan = j
                        continue
                    else:
                        result[tihuan] = result[tihuan] + 1
                        tihuan = ''
                        break
        except Exception:
            pass
    for i in result:
        result_list.append({"value": result[i], "name": i+'分钟左右'})
    return resp_200(msg="查询成功！", data=result_list)


@router.get('/film_from_country', summary="国家电影出产量（不少于10部）")
async def excel():
    # filename是文件的路径名称
    workbook = xlrd2.open_workbook('./models/film_data.xls')
    # 通过sheet名称获取
    table = workbook.sheet_by_name(sheet_name='Sheet1')
    table_list = table.col_values(colx=3, start_rowx=1, end_rowx=None)
    result = {"中国":0}
    result_list=[]
    agin_list = ["中国大陆", "中国香港", "中国台湾", '香港']
    del_list=[]
    # 数据统计
    for i in range(len(table_list)):
        i_data = re.split("[ /,无N]", table_list[i])
        for data in i_data:
            if data=='':
                break
            if data in agin_list:
                result["中国"] = result["中国"] + 1
                break
            if data not in result:
                result[data] = 0
            else:
                result[data] = result[data] + 1
    # 数据筛选（不少于10部）
    for i in result:
        if result[i]<10:
            del_list.append(i)
    for i in del_list:
        del result[i]
    # 数据重构
    for i in result:
        result_list.append({"name":i,"value":result[i]})
    return resp_200(msg="查询成功！", data=result_list)
